package com.erdos.common.util.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import javax.swing.table.TableModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.erdos.common.util.BeanUtils;


public class ExcelWriter {

	private static final Log logger = LogFactory.getLog(ExcelWriter.class);
	private static Map<String,Integer> colorMap;
    /**
     * 
     * @param formatStream
     * @param outStream
     * @param sheetNames
     * @param tables
     * @param regions
     *            Regions for per sheet
     * @param removeUnusedSheets
     * @param groupRowNum
     *            -1: no group row; >=0 : row number of group row
     *  
     */
    public static void write(InputStream formatStream, OutputStream outStream,
            String[] sheetNames, TableModel[] tables, List<Region>[] regions,
            boolean removeUnusedSheets, int groupRowNum) {
    	logger.info("Excel write1 start!");
        HSSFWorkbook wb = null;
        HSSFSheet sheet = null;

        try {
            if (null != formatStream) {
                POIFSFileSystem fs = new POIFSFileSystem(formatStream);
                wb = new HSSFWorkbook(fs);
                //formatStream.close();
            } else {
                wb = new HSSFWorkbook();
            }

            HSSFDataFormat format = wb.createDataFormat();

            HSSFCellStyle numericStyle = wb.createCellStyle();
            numericStyle.setDataFormat(format
                    .getFormat("###,###,###,###,###.##"));

            HSSFCellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd HH:mm:ss"));

            //write tables
            for (int i = 0; null != sheetNames && i < sheetNames.length; i++) {
                sheet = wb.getSheet(sheetNames[i]);
                if (sheet == null) {
                    if (wb.getNumberOfSheets()>0) {
                        sheet = wb.cloneSheet(0);
                    }
                    if (sheet == null)
                        sheet = wb.createSheet();
                    wb.setSheetName(wb.getNumberOfSheets() - 1, sheetNames[i],
                            HSSFWorkbook.ENCODING_UTF_16);
                }

                if (tables[i] != null) {
                    int rowCount = tables[i].getRowCount();
                    int colCount = tables[i].getColumnCount();
                    if (groupRowNum>=0&&sheet.getRow(groupRowNum) != null) { // group row format
                        sheet.shiftRows(groupRowNum, groupRowNum, rowCount
                                - groupRowNum - 1);
                        logger.debug("groupRowNum:"+groupRowNum);
                        sheet.removeRow(sheet.getRow(groupRowNum));
                    }
                    boolean isNewRow;
                    for (short rowIndex = 0; rowIndex < rowCount; rowIndex++) {
                        HSSFRow row = sheet.getRow(rowIndex);
                        if (row == null) {
                            row = sheet.createRow(rowIndex);
                            isNewRow = true;
                        } else
                            isNewRow = false;
                        for (short colIndex = 0; colIndex < colCount; colIndex++) {
                            HSSFCell cell = row.getCell(colIndex);
                            Object value = tables[i].getValueAt(rowIndex,
                                    colIndex);
                            if (cell == null) {
                                cell = row.createCell(colIndex);
                                if (isNewRow) {
                                    if (rowIndex > 1) {
                                        cell.setCellStyle(sheet.getRow(
                                                rowIndex - 1).getCell(colIndex)
                                                .getCellStyle());
                                    } else if (colIndex > 1) {
                                        cell.setCellStyle(sheet
                                                .getRow(rowIndex).getCell(
                                                        (short) (colIndex - 1))
                                                .getCellStyle());
                                    }

                                } else if (colIndex > 1) {
                                    cell.setCellStyle(sheet.getRow(rowIndex)
                                            .getCell((short) (colIndex - 1))
                                            .getCellStyle());
                                } else if (rowIndex > 1) {
                                    cell.setCellStyle(sheet
                                            .getRow(rowIndex - 1).getCell(
                                                    colIndex).getCellStyle());
                                } else {
                                    if (value != null) {
                                        if (value instanceof Calendar) {
                                            cell.getCellStyle().setDataFormat(
                                                    dateStyle.getDataFormat());
                                        } else if (value instanceof Date) {
                                            cell.getCellStyle().setDataFormat(
                                                    dateStyle.getDataFormat());
                                        } else if (value instanceof Number) {
                                            cell.getCellStyle().setDataFormat(
                                                    numericStyle
                                                            .getDataFormat());
                                        } else {
                                            cell
                                                    .setEncoding(HSSFCell.ENCODING_UTF_16);
                                        }
                                    }

                                }
                            }
                            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                            if (value != null) {
                                if (value instanceof Boolean) {
                                    cell.setCellValue(((Boolean) value)
                                            .booleanValue());
                                } else if (value instanceof Calendar) {
                                    cell.setCellValue((Calendar) value);
                                } else if (value instanceof Date) {
                                    cell.setCellValue((Date) value);
                                } else if (value instanceof Number) {
                                    cell.setCellValue(((Number) value)
                                            .doubleValue());
                                } else if (value instanceof ExcelFormula) {
                                    cell.setCellFormula(((ExcelFormula) value)
                                            .getFormula());
                                } else {
                                    cell.setCellValue(value.toString());
                                }
                            }

                        }
                    }
                }
                if ((regions != null) && (regions.length > i)
                        && (regions[i] != null)) {
                    List<Region> regionArray = regions[i];
                    for (int j = 0; j < regionArray.size(); j++)
                        sheet.addMergedRegion((Region) regionArray.get(j));
                }
            }//for(int i=0; null!=sheetName && i<sheetName.length;i++)

            if (removeUnusedSheets) { //remove unused sheet
                HashSet<String> sheetNameSet = new HashSet<String>();
                for (int i = 0; sheetNames != null && i < sheetNames.length; i++) {
                    sheetNameSet.add(sheetNames[i]);
                }
                int sheetCount = wb.getNumberOfSheets();
                String sheetName = null;
                for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
                    sheetName = wb.getSheetName(sheetIndex);
                    if (!sheetNameSet.contains(sheetName)) {
                        wb.removeSheetAt(sheetIndex);
                        sheetIndex--;
                        sheetCount--;
                    }
                }
            }
            if (wb.getNameIndex("Print_Area") >= 0) {
                wb.removeName("Print_Area");
            }
            if (null != outStream) {
                wb.write(outStream);
                //outStream.close();
            }
        } catch (Throwable t) {
            System.out
                    .println("[ExcelWriter.write] [EXCEPTION] ---------- [BEGIN] ----------");
            t.printStackTrace();
            System.out
                    .println("[ExcelWriter.write] [EXCEPTION] ---------- [END] ----------");
        } finally {
            //if(formatStream != null) try { formatStream.close(); }
            // catch(Exception e1) {}
            //if(outStream != null) try { outStream.close(); } catch(Exception
            // e1) {}
        }
    }
    public static void write(InputStream formatStream, OutputStream outStream,
    		String[] sheetNames, TableModel[] tables, List<Region>[] regions,TableModel[] styleTable,
    		boolean removeUnusedSheets, int groupRowNum) {
    	logger.debug("*********Excel write 2 start!********");
    	HSSFWorkbook wb = null;
    	HSSFSheet sheet = null;
    	
    	try {
    		if (null != formatStream) {
    			POIFSFileSystem fs = new POIFSFileSystem(formatStream);
    			wb = new HSSFWorkbook(fs);
    			logger.debug("create wb from formatStream");
    			//formatStream.close();
    		} else {
    			wb = new HSSFWorkbook();
    			logger.debug("create wb from non");
    		}

    		HSSFDataFormat format = wb.createDataFormat();
    		
    		HSSFCellStyle numericStyle = wb.createCellStyle();
    		numericStyle.setDataFormat(format
    				.getFormat("###,###,###,###,###.##"));
    		
    		HSSFCellStyle dateStyle = wb.createCellStyle();
    		dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd HH:mm:ss"));
    		
    		//write tables
    		for (int i = 0; null != sheetNames && i < sheetNames.length; i++) {
                Map styleMap = new HashMap();
    			sheet = wb.getSheet(sheetNames[i]);
    			if (sheet == null) {
    				if (wb.getNumberOfSheets()>0) {
    					sheet = wb.cloneSheet(0);
    				}
    				if (sheet == null)
    					sheet = wb.createSheet();
    				wb.setSheetName(wb.getNumberOfSheets() - 1, sheetNames[i],
    						HSSFWorkbook.ENCODING_UTF_16);
    			}
    			
    			if (tables[i] != null) {
    				int rowCount = tables[i].getRowCount();
    				int colCount = tables[i].getColumnCount();
    				logger.debug("groupRowNum:"+groupRowNum);
    				//logger.debug("sheet.getRow(groupRowNum):"+sheet.getRow(groupRowNum));
    				if (groupRowNum>=0&&sheet.getRow(groupRowNum) != null) { // group row format
    					sheet.shiftRows(groupRowNum, groupRowNum, rowCount
    							- groupRowNum - 1);
    					
    				sheet.removeRow(sheet.getRow(groupRowNum));
    				}
    				boolean isNewRow;
    				for (short rowIndex = 0; rowIndex < rowCount; rowIndex++) {
    					HSSFRow row = sheet.getRow(rowIndex);
    					if (row == null) {
    						row = sheet.createRow(rowIndex);
    						isNewRow = true;
    					} else
    						isNewRow = false;
    					for (short colIndex = 0; colIndex < colCount; colIndex++) {
    						HSSFCell cell = row.getCell(colIndex);
    						Object value = tables[i].getValueAt(rowIndex,
    								colIndex);
    						if (cell == null) {
    							cell = row.createCell(colIndex);
    							if (isNewRow) {
    								if (rowIndex > 1) {
    									cell.setCellStyle(sheet.getRow(
    											rowIndex - 1).getCell(colIndex)
    											.getCellStyle());
    								} else if (colIndex > 1) {
    									cell.setCellStyle(sheet
    											.getRow(rowIndex).getCell(
    													(short) (colIndex - 1))
    													.getCellStyle());
    								}
    								
    							} else if (colIndex > 1) {
    								cell.setCellStyle(sheet.getRow(rowIndex)
    										.getCell((short) (colIndex - 1))
    										.getCellStyle());
    							} else if (rowIndex > 1) {
    								cell.setCellStyle(sheet
    										.getRow(rowIndex - 1).getCell(
    												colIndex).getCellStyle());
    							} else {
    								if (value != null) {
    									if (value instanceof Calendar) {
    										cell.getCellStyle().setDataFormat(
    												dateStyle.getDataFormat());
    									} else if (value instanceof Date) {
    										cell.getCellStyle().setDataFormat(
    												dateStyle.getDataFormat());
    									} else if (value instanceof Number) {
    										cell.getCellStyle().setDataFormat(
    												numericStyle
    												.getDataFormat());
    									} else {
    										cell
    										.setEncoding(HSSFCell.ENCODING_UTF_16);
    									}
    								}
    								
    							}
    						}
    						cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                            if(styleTable != null){
                                String style =(String) styleTable[i].getValueAt(rowIndex,
                                        colIndex);
                                if(style!=null){
                                	cell.setCellStyle(createStyleWithCache(wb, styleMap,  style,  cell, value));
                                }
                            }
                           
    						if (value != null) {
    							if (value instanceof Boolean) {
    								cell.setCellValue(((Boolean) value)
    										.booleanValue());
    							} else if (value instanceof Calendar) {
    								cell.setCellValue((Calendar) value);
    							} else if (value instanceof Date) {
    								cell.setCellValue((Date) value);
    							} else if (value instanceof Number) {
    								cell.setCellValue(((Number) value)
    										.doubleValue());
    							} else if (value instanceof ExcelFormula) {
    								cell.setCellFormula(((ExcelFormula) value)
    										.getFormula());
    							} else {
    								cell.setCellValue(value.toString());
    							}
    						}
    						
    										
    					}
    				}
    			}
    			if ((regions != null) && (regions.length > i)
    					&& (regions[i] != null)) {
    				List regionArray = regions[i];
    				for (int j = 0; j < regionArray.size(); j++)
    					sheet.addMergedRegion((Region) regionArray.get(j));
    			}
    		}//for(int i=0; null!=sheetName && i<sheetName.length;i++)
    		
    		if (removeUnusedSheets) { //remove unused sheet
    			HashSet sheetNameSet = new HashSet();
    			for (int i = 0; sheetNames != null && i < sheetNames.length; i++) {
    				sheetNameSet.add(sheetNames[i]);
    			}
    			int sheetCount = wb.getNumberOfSheets();
    			String sheetName = null;
    			for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
    				sheetName = wb.getSheetName(sheetIndex);
    				if (!sheetNameSet.contains(sheetName)) {
    					wb.removeSheetAt(sheetIndex);
    					sheetIndex--;
    					sheetCount--;
    				}
    			}
    		}
    		if (wb.getNameIndex("Print_Area") >= 0) {
    			wb.removeName("Print_Area");
    		}
    		if (null != outStream) {
    			wb.write(outStream);
    			//outStream.close();
    		}
    	} catch (Throwable t) {
    		System.out
    		.println("[ExcelWriter.write] [EXCEPTION] ---------- [BEGIN] ----------");
    		t.printStackTrace();
    		System.out
    		.println("[ExcelWriter.write] [EXCEPTION] ---------- [END] ----------");
    	} finally {
    		//if(formatStream != null) try { formatStream.close(); }
    		// catch(Exception e1) {}
    		//if(outStream != null) try { outStream.close(); } catch(Exception
    		// e1) {}
    	}
    	logger.info("*********Excel Write2 end!***********");
    }

    private static short getColorIndexByName(String style) {
		if( colorMap == null ){
			colorMap  = new HashMap<String, Integer>();
			colorMap.put("BLACK",new Integer(HSSFColor.BLACK.index));
			colorMap.put("BROWN",new Integer(HSSFColor.BROWN.index));
			colorMap.put("OLIVE_GREEN",new Integer(HSSFColor.OLIVE_GREEN.index));
			colorMap.put("DARK_GREEN",new Integer(HSSFColor.DARK_GREEN.index));
			colorMap.put("DARK_TEAL",new Integer(HSSFColor.DARK_TEAL.index));
			colorMap.put("DARK_BLUE",new Integer(HSSFColor.DARK_BLUE.index));
			colorMap.put("INDIGO",new Integer(HSSFColor.INDIGO.index));
	        colorMap.put("GREY_80_PERCENT",new Integer(HSSFColor.GREY_80_PERCENT.index));
	        colorMap.put("ORANGE",new Integer(HSSFColor.ORANGE.index));
	        colorMap.put("DARK_YELLOW",new Integer(HSSFColor.DARK_YELLOW.index));
	        colorMap.put("GREEN",new Integer(HSSFColor.GREEN.index));
	        colorMap.put("TEAL",new Integer(HSSFColor.TEAL.index));
	        colorMap.put("BLUE",new Integer(HSSFColor.BLUE.index));
	        colorMap.put("BLUE_GREY",new Integer(HSSFColor.BLUE_GREY.index));
	        colorMap.put("GREY_50_PERCENT",new Integer(HSSFColor.GREY_50_PERCENT.index));
	        colorMap.put("RED",new Integer(HSSFColor.RED.index));
	        colorMap.put("LIGHT_ORANGE",new Integer(HSSFColor.LIGHT_ORANGE.index));
	        colorMap.put("LIME",new Integer(HSSFColor.LIME.index));
	        colorMap.put("SEA_GREEN",new Integer(HSSFColor.SEA_GREEN.index));
	        colorMap.put("AQUA",new Integer(HSSFColor.AQUA.index));
	        colorMap.put("LIGHT_BLUE",new Integer(HSSFColor.LIGHT_BLUE.index));
	        colorMap.put("VIOLET",new Integer(HSSFColor.VIOLET.index));
	        colorMap.put("GREY_40_PERCENT",new Integer(HSSFColor.GREY_40_PERCENT.index));
	        colorMap.put("PINK",new Integer(HSSFColor.PINK.index));
	        colorMap.put("GOLD",new Integer(HSSFColor.GOLD.index));
	        colorMap.put("YELLOW",new Integer(HSSFColor.YELLOW.index));
	        colorMap.put("BRIGHT_GREEN",new Integer(HSSFColor.BRIGHT_GREEN.index));
	        colorMap.put("TURQUOISE",new Integer(HSSFColor.TURQUOISE.index));
	        colorMap.put("DARK_RED",new Integer(HSSFColor.DARK_RED.index));
	        colorMap.put("SKY_BLUE",new Integer(HSSFColor.SKY_BLUE.index));
	        colorMap.put("PLUM",new Integer(HSSFColor.PLUM.index));
	        colorMap.put("GREY_25_PERCENT",new Integer(HSSFColor.GREY_25_PERCENT.index));
	        colorMap.put("ROSE",new Integer(HSSFColor.ROSE.index));
	        colorMap.put("LIGHT_YELLOW",new Integer(HSSFColor.LIGHT_YELLOW.index));
	        colorMap.put("LIGHT_GREEN",new Integer(HSSFColor.LIGHT_GREEN.index));
	        colorMap.put("LIGHT_TURQUOISE",new Integer(HSSFColor.LIGHT_TURQUOISE.index));	       
	        colorMap.put("PALE_BLUE",new Integer(HSSFColor.PALE_BLUE.index));
	        colorMap.put("LAVENDER",new Integer(HSSFColor.LAVENDER.index));
	        colorMap.put("WHITE",new Integer(HSSFColor.WHITE.index));
	        colorMap.put("CORNFLOWER_BLUE",new Integer(HSSFColor.CORNFLOWER_BLUE.index));
	        colorMap.put("LEMON_CHIFFON",new Integer(HSSFColor.LEMON_CHIFFON.index));
	        colorMap.put("MAROON",new Integer(HSSFColor.MAROON.index));
	        colorMap.put("ORCHID",new Integer(HSSFColor.ORCHID.index));
	        colorMap.put("CORAL",new Integer(HSSFColor.CORAL.index));
	        colorMap.put("ROYAL_BLUE",new Integer(HSSFColor.ROYAL_BLUE.index));
	        colorMap.put("LIGHT_CORNFLOWER_BLUE",new Integer(HSSFColor.LIGHT_CORNFLOWER_BLUE.index));
		}
		Integer index =  (Integer)colorMap.get(style.toUpperCase());
		return index!=null?index.shortValue():(short)-1;
	}
    
    
    
    
    public static void write(InputStream formatStream, OutputStream outStream,
            String[] sheetNames, TableModel[] tables, List<Region>[] regions,TableModel[] styleTable,
            boolean removeUnusedSheets, int groupRowNum,Map imageSheets) {
        HSSFWorkbook wb = null;
        HSSFSheet sheet = null;
        
        try {
            if (null != formatStream) {
                POIFSFileSystem fs = new POIFSFileSystem(formatStream);
                wb = new HSSFWorkbook(fs);
                //formatStream.close();
            } else {
                wb = new HSSFWorkbook();
            }
            HSSFDataFormat format = wb.createDataFormat();
            HSSFCellStyle numericStyle = wb.createCellStyle();
            numericStyle.setDataFormat(format
                    .getFormat("###,###,###,###,###.##"));
            HSSFCellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(format.getFormat("yyyy/MM/dd HH:mm:ss"));
            
           
            //write tables
            for (int i = 0; null != sheetNames && i < sheetNames.length; i++) {
                Map styleMap = new HashMap();
                sheet = wb.getSheet(sheetNames[i]);
                if (sheet == null) {
                    if (wb.getNumberOfSheets()>0) {
                        sheet = wb.cloneSheet(0);
                    }
                    if (sheet == null){
                        sheet = wb.createSheet();
                        }
                    wb.setSheetName(wb.getNumberOfSheets() - 1, sheetNames[i],
                            HSSFWorkbook.ENCODING_UTF_16);
                }
                if (tables[i] != null) {
                    int rowCount = tables[i].getRowCount();
                    int colCount = tables[i].getColumnCount();
                    if (groupRowNum>=0&&sheet.getRow(groupRowNum) != null) {
                    	//Shifts rows between startRow and endRow n number of rows.
                        sheet.shiftRows(groupRowNum, groupRowNum, rowCount
                                - groupRowNum - 1);
                        //Remove a row from this sheet.
                        sheet.removeRow(sheet.getRow(groupRowNum));
                    }
                    boolean isNewRow;
                    for (short rowIndex = 0; rowIndex < rowCount; rowIndex++) {
                        HSSFRow row = sheet.getRow(rowIndex);
                        if (row == null) {
                            row = sheet.createRow(rowIndex);
                            isNewRow = true;
                        } else{
                            isNewRow = false;
                        }
                        for (short colIndex = 0; colIndex < colCount; colIndex++) {
                            HSSFCell cell = row.getCell(colIndex);
                            Object value = tables[i].getValueAt(rowIndex,
                                    colIndex);
                            if (cell == null) {
                                cell = row.createCell(colIndex);
                                if (isNewRow) {
                                    if (rowIndex > 1) {
                                        cell.setCellStyle(sheet.getRow(
                                                rowIndex - 1).getCell(colIndex)
                                                .getCellStyle());
                                    } else if (colIndex > 1) {
                                        cell.setCellStyle(sheet
                                                .getRow(rowIndex).getCell(
                                                        (short) (colIndex - 1))
                                                        .getCellStyle());
                                    }
                                    
                                } else if (colIndex > 1) { 
                                    if (value != null) {
                                        cell.setCellStyle(sheet.getRow(rowIndex)
                                                .getCell((short) (colIndex - 1))
                                                .getCellStyle());
                                    }
                                } else if (rowIndex > 1) {
                                    if (value != null) {
                                        cell.setCellStyle(sheet
                                                .getRow(rowIndex - 1).getCell(
                                                        colIndex).getCellStyle());
                                    }
                                } else {
                                    if (value != null) {
                                        if (value instanceof Calendar) {
                                            cell.getCellStyle().setDataFormat(
                                                    dateStyle.getDataFormat());
                                        } else if (value instanceof Date) {
                                            cell.getCellStyle().setDataFormat(
                                                    dateStyle.getDataFormat());
                                        } else if (value instanceof Number) {
                                            cell.getCellStyle().setDataFormat(
                                                    numericStyle
                                                    .getDataFormat());
                                        } else {
                                            cell
                                            .setEncoding(HSSFCell.ENCODING_UTF_16);
                                        }
                                    }
                                    
                                }
                            }
                            if(styleTable != null){
                                String style =(String) styleTable[i].getValueAt(rowIndex,
                                        colIndex);
                                if(style!=null){
                                	cell.setCellStyle(createStyleWithCache(wb, styleMap,  style,  cell, value));
                                }
                            }
                            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                            
                            if (value != null) {
                                if (value instanceof Boolean) {
                                    cell.setCellValue(((Boolean) value)
                                            .booleanValue());
                                } else if (value instanceof Calendar) {
                                    cell.setCellValue((Calendar) value);
                                } else if (value instanceof Date) {
                                    cell.setCellValue((Date) value);
                                } else if (value instanceof Number) {
                                    cell.setCellValue(((Number) value)
                                            .doubleValue());
                                } else if (value instanceof ExcelFormula) {
                                    cell.setCellFormula(((ExcelFormula) value)
                                            .getFormula());
                                } else {
                                    cell.setCellValue(value.toString());
                                }
                            }
                            
                            
                        }
                    }
                }
                if ((regions != null) && (regions.length > i)
                        && (regions[i] != null)) {
                    List regionArray = regions[i];
                    for (int j = 0; j < regionArray.size(); j++)
                        sheet.addMergedRegion((Region) regionArray.get(j));
                }
            }
            if( imageSheets != null ){
                for (Iterator iter = imageSheets.keySet().iterator(); iter.hasNext();) {
                    String sheetName = (String) iter.next();
                    sheet = wb.getSheet(sheetName);
                    if (sheet == null) {
                        if (wb.getNumberOfSheets()>0) {
                            sheet = wb.cloneSheet(0);
                        }
                        if (sheet == null)
                            sheet = wb.createSheet();
                        wb.setSheetName(wb.getNumberOfSheets() - 1, sheetName,
                                HSSFWorkbook.ENCODING_UTF_16);
                    }
                    ByteArrayOutputStream outputStream = (ByteArrayOutputStream)imageSheets.get(sheetName);
                    HSSFPatriarch Patriarch = sheet.createDrawingPatriarch();
                    HSSFClientAnchor Anchor = new HSSFClientAnchor(0, 0, 1023, 255,
                            (short) 0, 0, (short) 13, 30); 
                    Patriarch.createPicture(Anchor, wb.addPicture(outputStream
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                }
            }
            
            if (removeUnusedSheets) { //remove unused sheet
                HashSet<String> sheetNameSet = new HashSet<String>();
                for (int i = 0; sheetNames != null && i < sheetNames.length; i++) {
                    sheetNameSet.add(sheetNames[i]);
                }
                
                if(imageSheets != null){
                    for (Iterator iter = imageSheets.keySet().iterator(); iter.hasNext();) {
                        String sheetName = (String) iter.next();
                        sheetNameSet.add(sheetName);
                        
                    }
                }

                int sheetCount = wb.getNumberOfSheets();
                String sheetName = null;
                for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
                    sheetName = wb.getSheetName(sheetIndex);
                    if (!sheetNameSet.contains(sheetName)) {
                        wb.removeSheetAt(sheetIndex);
                        sheetIndex--;
                        sheetCount--;
                    }
                }
            }
            if (wb.getNameIndex("Print_Area") >= 0) {
                wb.removeName("Print_Area");
            }
            if (null != outStream) {
                wb.write(outStream);
                //outStream.close();
            }
        } catch (Throwable t) {
            System.out
            .println("[ExcelWriter.write] [EXCEPTION] ---------- [BEGIN] ----------");
            t.printStackTrace();
            System.out
            .println("[ExcelWriter.write] [EXCEPTION] ---------- [END] ----------");
        } finally {
            //if(formatStream != null) try { formatStream.close(); }
            // catch(Exception e1) {}
            //if(outStream != null) try { outStream.close(); } catch(Exception
            // e1) {}
        }
    }
	/**
     * 
     * @param formatStream
     * @param outStream
     * @param sheetNames
     * @param tables
     */
    public static void write(InputStream formatStream, OutputStream outStream,
            String[] sheetNames, TableModel[] tables,
            boolean removeUnusedSheets, int groupRowNum) {
        write(formatStream, outStream, sheetNames, tables, null, true,
                groupRowNum);
    }

    public static void write(InputStream formatStream, OutputStream outStream,
            String[] sheetNames, TableModel[] tables) {
        write(formatStream, outStream, sheetNames, tables, true, 2);
    }

    public static void write(InputStream formatStream, OutputStream outStream,
            String[] sheetNames, TableModel[] tables, int groupRowNum) {
        write(formatStream, outStream, sheetNames, tables, true, groupRowNum);
    }
    
    public static HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREEN.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLUE.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);
        
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short)12);
        font.setFontName("Arial");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        return style;
    }
    
    public static HSSFCellStyle renderColor(HSSFCellStyle curStyle,short color) {
        //HSSFCellStyle style = wb.createCellStyle();
    	HSSFCellStyle style = (HSSFCellStyle)BeanUtils.cloneBean(curStyle);
        style.setFillForegroundColor(color);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        return style;
    }
    
    /**
     * create cellstyle based on cell, by setting fillColor, fontName and fontColor.
     * @param wb
     * @param fillColor
     * @param fontName
     * @param fontColor
     * @param cell
     * @return
     */
    private static HSSFCellStyle createStyle(HSSFWorkbook wb, short fillColor, String fontName, short fontColor, HSSFCell cell) {
    	HSSFCellStyle style = null;
    	HSSFFont oldFont = wb.getFontAt(cell.getCellStyle().getFontIndex());
    	if(fillColor == cell.getCellStyle().getFillForegroundColor()  
    			&& (fontName!=null && fontName.equals(oldFont.getFontName()))
    			&& fontColor == oldFont.getColor()
    			){
    		style =  cell.getCellStyle();
    	}else{
	        style = wb.createCellStyle();
	        BeanUtils.copyProperties(style,cell.getCellStyle());
	        
	        if(fillColor != -1){
	        	 style.setFillForegroundColor(fillColor);
	        	 style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        }
	        if(fontName!=null || fontColor !=-1 ){
	        	HSSFFont font = wb.createFont();
	        	BeanUtils.copyProperties(font,wb.getFontAt(cell.getCellStyle().getFontIndex()));
	        	if(fontName != null){
	        		font.setFontName(fontName);
	        	}
	        	if(fontColor != -1){
	        		font.setColor(fontColor);
	        	}
	        	style.setFont(font);
	        }
    	}
        return style;
   }

    /**
     * create cellstyle based on cell, by setting fillColor, fontName and fontColor.
     * And store into cache styleMap. 
     * style string format is fillColorName:fontName:fontcolorName
     * @param wb
     * @param styleMap
     * @param style 
     * @param cell
     * @param cellValue
     * @return
     */
    private static HSSFCellStyle createStyleWithCache(HSSFWorkbook wb, 
    		Map<String,HSSFCellStyle> styleMap, 
    		String style, 
    		HSSFCell cell,
    		Object cellValue) {
    	HSSFCellStyle cellStyle = cell.getCellStyle(); //default return the current style of the cell
	    if( style != null ){
	    	HSSFCellStyle newCellStyle;
			short newFillColorIndex = -1;
            String newFontName = null;
            short newFontColorIndex = -1;
            HSSFFont oldFont = null;
	    	
	    	String[] styleNames = style.split(":"); //color:font
	        
	        if(styleNames!=null){
	        	if(styleNames.length>=1){
	        		newFillColorIndex = getColorIndexByName(styleNames[0]);
	        		//logger.debug("newFillColorIndex:"+newFillColorIndex);
	        	}                                    	
	        	if(styleNames.length>=2){
	        		newFontName = styleNames[1];
	        		//logger.debug("newFontName:"+newFontName);
	        	}                                    	
	        	if(styleNames.length>=3){
	        		newFontColorIndex = getColorIndexByName(styleNames[2]);
	        	}                                    	
	        }
	        oldFont =	wb.getFontAt(cell.getCellStyle().getFontIndex());	 
	        //logger.debug("oldFont:"+oldFont);
	        if(  ( ( newFillColorIndex != (short)-1)  && (cell.getCellStyle().getFillForegroundColor() != newFillColorIndex) )
	        			||  ( newFontName!=null && !newFontName.equals(oldFont.getFontName()) )
	        			||  ( ( newFontColorIndex != (short)-1)  && (oldFont.getColor() != newFontColorIndex) )
	        			){
	        	
	        	StringBuffer sb = new StringBuffer();
	        	if(newFillColorIndex == -1){
	        		newFillColorIndex = cell.getCellStyle().getFillForegroundColor() ;
	        	}
	        	if(newFontName == null || newFontName.length()==0){
	        		newFontName = oldFont.getFontName();
	        	}
	        	if(newFontColorIndex == -1){
	        		newFontColorIndex = oldFont.getColor() ;
	        	}
	        	
	        	if(cellValue!=null){
	        		sb.append(cellValue.getClass().getName());
	        	}
	        	sb.append("+");

	        	sb.append(newFillColorIndex); sb.append("+");
	        	sb.append(newFontName);       sb.append("+");
	        	sb.append(newFontColorIndex); sb.append("+");
	        	
	        	if(styleNames!=null && styleNames.length>3){
	        		for(int i=3;i<styleNames.length; i++){
	        			sb.append(styleNames[i]); sb.append("+");
	        		}
	        	}
	        
        		newCellStyle = (HSSFCellStyle) styleMap.get(sb.toString());
                if (newCellStyle == null) {
                	newCellStyle = createStyle(wb, newFillColorIndex, newFontName, newFontColorIndex, cell);
                    styleMap.put(sb.toString(), newCellStyle);
                }
                cellStyle = newCellStyle;
	        }
	    }
	    return cellStyle;
	}  

    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("Arial");
        style.setFont(font);
        return style;
    }

    private static final short WIDTH_UNIT = (short) (short) ((50 * 1) / ((double) 1 / 20));

    /**
     * Generally, model id width is 8.
     */
    public static void setCellWidth(HSSFSheet sheet, HSSFCell cell, int width) {
        short cellNo = cell.getCellNum();
        sheet.setColumnWidth(cellNo, (short) (width * WIDTH_UNIT));
    }

    public static void setCellValue(HSSFCell cell, Object value) {
    	setCellValue(cell, value, null);
    }
    
    public static void setCellValue(HSSFCell cell, Object value,
            HSSFCellStyle style) {
    	
        if (style != null) {
            cell.setCellStyle(style);
        }
        if (value == null) {
            return;
        }
        if (value instanceof String) {
            cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        } else if (value instanceof ExcelFormula) {
            cell.setCellFormula(((ExcelFormula) value).getFormula());
        } else {
            cell.setCellValue(value.toString());
        }
    }

    public static void setContentType(HttpServletResponse response,
            String fileName) {

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=\""
                + fileName + "\"");
        response.setHeader("Pragma",""); 
		response.setHeader("Cache-Control","");
    }

    /**
     * Add the Region into the sheet.
     * @param sheet the sheet that the region to merge belong to
     * @param regionList List of Region objects
     */
    public static void mergeCell(HSSFSheet sheet, List<Region> regionList) {
        if (regionList == null || regionList.size() == 0) {
            return;
        }
        for (int i = 0; i < regionList.size(); i++) {
            Region r = (Region) regionList.get(i);
            sheet.addMergedRegion(r);
        }
    }
    
    /**
     * Create a new row in newRowIndex and copy the cell value of the row rowIndex
     * into this new row.
     */
    public static void copyRow(HSSFSheet sheet, int rowIndex, int newRowIndex) {
		HSSFRow row = sheet.getRow(rowIndex);
		if (row == null) {
			logger.error("The row<" + rowIndex + "> in sheet do not exist");
			return;
		}
		HSSFRow newRow = sheet.createRow(newRowIndex);
		copy(row, newRow);
	}

	private static void copy(HSSFRow row, HSSFRow newRow) {

		for (Iterator it = row.cellIterator(); it.hasNext();) {
			HSSFCell cell = (HSSFCell) it.next();
			short i = cell.getCellNum();
			HSSFCell newCell = newRow.createCell(i);
			newCell.setCellStyle(cell.getCellStyle());
			int cellType = cell.getCellType();
			if (HSSFCell.CELL_TYPE_STRING == cellType) {
				String s = cell.getStringCellValue();
				newCell.setCellValue(s);
			} else if (HSSFCell.CELL_TYPE_NUMERIC == cellType) {
				double d = cell.getNumericCellValue();
				newCell.setCellValue(d);
			} else if (HSSFCell.CELL_TYPE_FORMULA == cellType) {
				String s = cell.getCellFormula();
				newCell.setCellFormula(s);
			} else if (HSSFCell.CELL_TYPE_BOOLEAN == cellType) {
				boolean b = cell.getBooleanCellValue();
				newCell.setCellValue(b);
			} else if (HSSFCell.CELL_TYPE_BLANK == cellType) {
				// do nothing
			} else {
				logger.error("Unkonwn cell type <" + cellType + ">");
			}
		}
	}
	
	public static HSSFRow getRow(HSSFSheet sheet, int rowIndex) {
		HSSFRow row = sheet.getRow((short) rowIndex);
		if (row == null) {
			row = sheet.createRow((short) rowIndex);
		}
		return row;
	}

	public static HSSFCell getCell(HSSFRow row, int cellIndex) {
		HSSFCell cell = row.getCell((short) cellIndex);
		if (cell == null) {
			cell = row.createCell((short) cellIndex);
		}
		return cell;
	}
    
	public static HSSFCell getCell(HSSFSheet sheet, int rowIndex, int cellIndex) {
		HSSFRow row = getRow(sheet, rowIndex);
		HSSFCell cell = row.getCell((short) cellIndex);
		if (cell == null) {
			cell = row.createCell((short) cellIndex);
		}
		return cell;
	}

	public static void setCellValue(HSSFSheet sheet, int rowIndex,
			int cellIndex, Object value) {

		HSSFRow row = sheet.getRow((short) rowIndex);
		HSSFCell cell = row.getCell((short) cellIndex);
		setCellValue(cell, value);
	}
	
	public static void setCellValue(HSSFRow row, int cellIndex, Object value) {
		HSSFCell cell = row.getCell((short) cellIndex);
		setCellValue(cell, value);
	}

	/**
	 * renders the Excel's sheet by the specified formats.
	 * the parameter toWrite holds the Excel content, which will be converted into a InputStream first.
	 * and then construst a WorkBook for further process.
	 * parameter fowFormats holds the format defined in {@link #ExcelFormat ExcelFormat},
	 * the format in each index of rowFormats array will be applied to the HSSFRow of which row number 
	 * has  the same value with rowFormats array index.
	 * note, currently, this method only supports one sheet(i.e. the first sheet in the Excel).
	 * @param toWrite an ByteArrayOutputStream which holds the Excel content
	 * @param rowFormats holds the format to be applied to the specified row number
	 * @author sunf
	 * @since 5/22/07
	 */
	public static void renderFormatByRow(ByteArrayOutputStream toWrite, String[] rowFormats) {		
		try {
			InputStream input=convertToInputStream(toWrite);
			toWrite.flush();
			
			POIFSFileSystem fs = new POIFSFileSystem(input);
			HSSFWorkbook wb=new HSSFWorkbook(fs);
			int sheetSize=wb.getNumberOfSheets();
			ExcelFormat formatter=new ExcelFormat(wb);
			for(int i=0; i<sheetSize; i++) {
				HSSFSheet sheet=wb.getSheetAt(i);
				sheet.setDisplayGridlines(true);
				int rowSize=sheet.getPhysicalNumberOfRows();
				for(int r=0; r<rowSize; r++) {
					HSSFRow row=sheet.getRow(r);
					
					short cellSize=row.getLastCellNum() ;
					String formatStyle=rowFormats[r];
					
					for(short c=0; c<cellSize+1; c++) {
						HSSFCell cell=row.getCell(c);
						//if the cell to set is not a numeric type, ignores it
						formatter.setCellNumberFormat(cell, formatStyle);
					}
				}
			}

			toWrite.reset();
			wb.write(toWrite);
			toWrite.flush();
		} catch (IOException ex) {
			logger.error(ex);
		}
	}
	
	private static InputStream convertToInputStream(ByteArrayOutputStream toWrite) throws IOException {
		if(toWrite==null ) return null;
		return new ByteArrayInputStream(toWrite.toByteArray());		
	}
}